//
//  Database.m
//  iOsiris
//
//  Created by bram on 12/16/10.
//  Copyright 2010 __MyCompanyName__. All rights reserved.
//

#import "Database.h"

@implementation Database

//
// Create a new instance of the Database class.
//
- (id) init
{
	if ((self = [super init]))
	{
		[self initializeDatabase];
	}
	
	return self;
}

- (void) dataFilePath
{
	// create a writable copy of the default database from the bundle
	// in the application documentss directory
	BOOL success;
	NSFileManager *filemanager = [NSFileManager defaultManager];
	NSError *error;
	NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
	NSString *documentsDir = [paths objectAtIndex:0];
	writableDB = [documentsDir stringByAppendingPathComponent:kFilename];
	success = [filemanager fileExistsAtPath:writableDB];
	
	// the editable database already exists
	if (success) return;
	
	// the editable database does not exist
	NSString *defaultPath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:kFilename];
	success = [filemanager copyItemAtPath:defaultPath toPath:writableDB error:&error];
	if(!success){
		NSAssert1(0, @"failed to create writable database file:'%@',",[error localizedDescription]);
	}
}

//
// Initialize the database.
//
- (void) initializeDatabase
{
	// Get the database from the application bundle.
	
	[self dataFilePath];
	
	//NSString *path = [[NSBundle mainBundle] pathForResource:@"osiris" ofType:@"db"];
	
	if (sqlite3_open([writableDB UTF8String], &database) == SQLITE_OK)
	{
		//NSLog(@"Opening database");
	}
	else 
	{
		// Close database properly to clean up.
		sqlite3_close(database);
		
		NSAssert1(0, @"Failed to open database: '%s'.", sqlite3_errmsg(database));
	}
}

//
// Close the database.
//
- (void) closeDatabase
{
	// Close the database.
	if (sqlite3_close(database) != SQLITE_OK) 
	{
		NSAssert1(0, @"Error: failed to close database: '%s'.", sqlite3_errmsg(database));
	}
}

- (Account*) getAccountByName:(NSString *)name
{
	// The SQL statement
	NSString *sql = [[NSString alloc] initWithFormat: @"SELECT * FROM Accounts WHERE username = '%@'", name];
	Account *account = [[Account alloc] init];
	
	sqlite3_stmt *statement; // Result
	
	// Prepare the statement.
	int sqlResult = sqlite3_prepare_v2(database, [sql UTF8String], -1, &statement, NULL);
	
	if (sqlResult == SQLITE_OK) 
	{
		if (sqlite3_step(statement) == SQLITE_ROW)
		{
			char* username = (char*)sqlite3_column_text(statement, 1);
			char* password = (char*)sqlite3_column_text(statement, 2);
			char* study = (char*)sqlite3_column_text(statement, 6);
			
			account.userid = sqlite3_column_int(statement, 0);
			account.username = (username) ? [NSString stringWithUTF8String:username] : @"";
			account.password = (password) ? [NSString stringWithUTF8String:password] : @"";
			account.startYear = sqlite3_column_int(statement, 3);
			account.currentYear = sqlite3_column_int(statement, 4);
			account.currentPeriod = sqlite3_column_int(statement, 5);
			account.study = (study) ? [NSString stringWithUTF8String:study] : @"";
			account.fulltime = (bool)sqlite3_column_int(statement, 7);
			account.isStudent = (bool)sqlite3_column_int(statement, 7);
		}
	}
	
	// Finalise the statement to release its resources.
	sqlite3_finalize(statement);
	// Release the query string.
	[sql release];
	
	return account;
}

- (NSMutableArray *) getAllPeriods:(NSString *)year
{
	NSMutableArray *periods = [[[NSMutableArray alloc] init] autorelease];
	
	NSString *sql = [[NSString alloc] initWithFormat: @"SELECT perioddata.id,perioddata.periodname,perioddata.details,years.year,periods.period \
					 FROM perioddata,years,periods WHERE years.yearid=perioddata.yearid AND perioddata.periodid=periods.periodid AND years.year='%@'", year];
	
	
	sqlite3_stmt *statement;
	int sqlResult = sqlite3_prepare_v2(database, [sql UTF8String], -1, &statement, NULL);
	
	if(sqlResult == SQLITE_OK)
	{
		while(sqlite3_step(statement) == SQLITE_ROW)
		{
			Selection *selection = [[Selection alloc] init];
			
			char *name = (char *)sqlite3_column_text(statement, 1);
			char *details = (char *)sqlite3_column_text(statement, 2);
			char *selectedYear = (char *)sqlite3_column_text(statement, 3);
			char *selectedPeriod = (char *)sqlite3_column_text(statement, 4);
			
			selection.id = sqlite3_column_int(statement, 0);
			selection.name = (name) ? [NSString stringWithUTF8String:name] : @"";
			selection.details = (details) ? [NSString stringWithUTF8String:details] : @"";
			selection.selectedYear = (selectedYear) ? [NSString stringWithUTF8String:selectedYear] : @"";
			selection.selectedPeriod = (selectedPeriod) ? [NSString stringWithUTF8String:selectedPeriod] : @"";
			
			[periods addObject:selection];
			[selection release];
		}
		sqlite3_finalize(statement);
	}
	else 
	{
		NSLog(@"Problem with the database");
		NSLog(@"%d", sqlResult);
	}
	
	return periods;
}

- (Selection *) getSelectedPeriodForYear:(NSInteger)year: (NSInteger)period 
{
	Selection *selection = [[[Selection alloc] init] autorelease];
	
	NSString *sql = [[NSString alloc] initWithFormat: @"SELECT perioddata.perioddataid,perioddata.periodname,perioddata.details, perioddata.lecturer, perioddata.result, years.year,periods.period \
					 FROM perioddata,years,periods WHERE years.yearid=perioddata.yearid AND perioddata.periodid=periods.periodid AND years.yearid='%d' AND periods.periodid='%d'" ,year ,period];
	
	sqlite3_stmt *statement;
	int sqlResult = sqlite3_prepare_v2(database, [sql UTF8String], -1, &statement, NULL);
	
	if(sqlResult == SQLITE_OK)
	{
		while(sqlite3_step(statement) == SQLITE_ROW)
		{
			char *name = (char *)sqlite3_column_text(statement, 1);
			char *details = (char *)sqlite3_column_text(statement, 2);
			char *lecturer = (char *)sqlite3_column_text(statement, 3);
			char *result = (char *)sqlite3_column_text(statement, 4);
			char *selectedYear = (char *)sqlite3_column_text(statement, 5);
			char *selectedPeriod = (char *)sqlite3_column_text(statement, 6);
			
			selection.id = sqlite3_column_int(statement, 0);
			selection.name = (name) ? [NSString stringWithUTF8String:name] : @"";
			selection.details = (details) ? [NSString stringWithUTF8String:details] : @"";
			selection.lecturer = (lecturer) ? [NSString stringWithUTF8String:lecturer] : @"";
			selection.result = (result) ? [NSString stringWithUTF8String:result] : @"";
			selection.selectedYear = (selectedYear) ? [NSString stringWithUTF8String:selectedYear] : @"";
			selection.selectedPeriod = (selectedPeriod) ? [NSString stringWithUTF8String:selectedPeriod] : @"";
			
			//[selection release];
		}
		sqlite3_finalize(statement);
	}
	else 
	{
		NSLog(@"Problem with the database");
		NSLog(@"%d", sqlResult);
	}
	
	return selection;
}

- (NSMutableArray *) getYears
{
	
	NSMutableArray *yearArray = [[NSMutableArray alloc] init];
	
	NSString *sql = [[NSString alloc] initWithFormat: @"SELECT yearid, year FROM Years"];
	
	sqlite3_stmt *statement;
	
	int sqlResult = sqlite3_prepare_v2(database, [sql UTF8String], -1, &statement, NULL);
	
	if(sqlResult == SQLITE_OK)
	{
		while(sqlite3_step(statement) == SQLITE_ROW)
		{
			int yearid = (int)sqlite3_column_int(statement, 0);
			char *year = (char *)sqlite3_column_text(statement, 1);
			
			IdValue *yearValue = [IdValue alloc];
			yearValue.id = yearid;
			yearValue.value = [[NSString alloc] initWithUTF8String:year]; // stringWithFormat:@"%@", year];
						
			[yearArray addObject:yearValue];
		}
		sqlite3_finalize(statement);
	}
	else 
	{
		NSLog(@"Problem with the database");
		NSLog(@"%d", sqlResult);
	}
	
	NSLog(@"array: %@", yearArray);
	
	return yearArray;
}

- (NSMutableArray *) getPeriods
{
	
	NSMutableArray *periodArray = [[NSMutableArray alloc] init];
	
	NSString *sql = [[NSString alloc] initWithFormat: @"SELECT Periods.periodid, Periods.period, PeriodData.periodname, PeriodData.yearID FROM Periods, PeriodData WHERE Periods.periodid=PeriodData.periodid"];
	
	sqlite3_stmt *statement;
	
	int sqlResult = sqlite3_prepare_v2(database, [sql UTF8String], -1, &statement, NULL);
	
	if(sqlResult == SQLITE_OK)
	{
		while(sqlite3_step(statement) == SQLITE_ROW)
		{
			int periodid = (int)sqlite3_column_int(statement, 0);
			char *period = (char *)sqlite3_column_text(statement, 1);
			char *periodName = (char *)sqlite3_column_text(statement, 2);
			int yearid = (int)sqlite3_column_int(statement, 3);

			
			IdValue *periodValue = [IdValue alloc];
			periodValue.id = periodid;
			periodValue.value = [[NSString alloc] initWithUTF8String:period]; // stringWithFormat:@"%@", year];
			periodValue.yearid = yearid;
			periodValue.periodName = [[NSString alloc] initWithUTF8String:periodName];
			
			[periodArray addObject:periodValue];
		}
		sqlite3_finalize(statement);
	}
	else 
	{
		NSLog(@"Problem with the database");
		NSLog(@"%d", sqlResult);
	}
	
	return periodArray;
}

- (NSInteger) getPeriodDataId:(NSInteger)year: (NSInteger)period
{
	NSString *sql = [[NSString alloc] initWithFormat: @"SELECT perioddataid FROM PeriodData WHERE yearid=%d AND periodid=%d", year, period];
	
	sqlite3_stmt *statement;
	int sqlResult = sqlite3_prepare_v2(database, [sql UTF8String], -1, &statement, NULL);
	
	int perioddataid = 0;
	
	if(sqlResult == SQLITE_OK)
	{
		if(sqlite3_step(statement) == SQLITE_ROW)
		{
			perioddataid = sqlite3_column_int(statement, 0);
		}
		sqlite3_finalize(statement);
	}
	else 
	{
		NSLog(@"Problem with the database");
		NSLog(@"%d", sqlResult);
	}
	
	
	return perioddataid;
}

- (int) getOpenRegisterCount:(NSInteger)period: (NSInteger) number: (NSInteger) userId
{
	int count;
	
	NSString *sql = [[NSString alloc] initWithFormat: @"SELECT COUNT(open) FROM COURSES WHERE PeriodDataId=%d and open=%d and UserId=%d", period, number, userId];
	sqlite3_stmt *statement;
	if(sqlite3_prepare_v2(database, [sql UTF8String], -1, &statement, NULL) == SQLITE_OK)
	{
		count = 0 ;
		while (sqlite3_step(statement) == SQLITE_ROW) {
			count = sqlite3_column_int(statement, 0);
		}
	}
	sqlite3_finalize(statement);
	
	NSLog(@"count: %d", count);
	return count;
}

- (int) getRegisteredCoursesCount:(NSInteger)period: (NSInteger) number: (NSInteger) userId
{
	int count;
	
	NSString *sql = [[NSString alloc] initWithFormat: @"SELECT COUNT(registered) FROM COURSES WHERE registered=%d and UserId=%d", number, userId];
	sqlite3_stmt *statement;
	if(sqlite3_prepare_v2(database, [sql UTF8String], -1, &statement, NULL) == SQLITE_OK)
	{
		count = 0 ;
		while (sqlite3_step(statement) == SQLITE_ROW) {
			count = sqlite3_column_int(statement, 0);
		}
	}
	sqlite3_finalize(statement);
	
	NSLog(@"count: %d", count);
	return count;
	
}

- (NSMutableArray *) openCourses:(NSInteger)perioddataid :(NSInteger)userId
{
	NSMutableArray *courseArray = [[NSMutableArray alloc] init];
	
	NSString *sql = [[NSString alloc] initWithFormat: @"SELECT Courses.CourseId,Courses.Date,Courses.Time,Courses.Location,Courses.Description, PeriodData.periodname, Courses.CourseHeader \
					 FROM Courses, PeriodData WHERE Courses.PeriodDataId=%d AND Courses.userId=%d AND Courses.open=1 AND PeriodData.perioddataid=%d", perioddataid, userId, perioddataid];
	
	sqlite3_stmt *statement;
	int sqlResult = sqlite3_prepare_v2(database, [sql UTF8String], -1, &statement, NULL);
	
	if(sqlResult == SQLITE_OK)
	{
		while(sqlite3_step(statement) == SQLITE_ROW)
		{
			Course *course = [[Course alloc] init];
					
			char *date = (char *)sqlite3_column_text(statement, 1);
			char *time = (char *)sqlite3_column_text(statement, 2);
			char *location = (char *)sqlite3_column_text(statement, 3);
			char *description = (char *)sqlite3_column_text(statement, 4);
			char *period = (char *)sqlite3_column_text(statement, 5);
			char *header = (char *)sqlite3_column_text(statement, 6);
			char *username = (char *)sqlite3_column_text(statement, 7);
			
			
			course.courseId = sqlite3_column_int(statement, 0);
			course.date = (date) ? [NSString stringWithUTF8String:date] : @"";
			course.time = (time) ? [NSString stringWithUTF8String:time] : @"";
			course.location = (location) ? [NSString stringWithUTF8String:location] : @"";
			course.period = (period) ? [NSString stringWithUTF8String:period] : @"";
			course.username = (username) ? [NSString stringWithUTF8String:username] : @"";
			course.description = (description) ? [NSString stringWithUTF8String:description] : @"";
			course.courseHeader = (header) ? [NSString stringWithUTF8String:header] : @"";
			
			[courseArray addObject:course];
			[course release];
		}
		sqlite3_finalize(statement);
	}
	else 
	{
		NSLog(@"Problem with the database");
		NSLog(@"%d", sqlResult);
	}	
	return courseArray;
}

- (void) registerTheCourse:(NSInteger) rowNr;
{
	NSString *sqlUpdate = [[NSString alloc] initWithFormat:@"UPDATE Courses SET registered=1, Open=0 WHERE CourseId=%d", rowNr];
	
	sqlite3_stmt *statement;
	
	if(sqlite3_prepare_v2(database, [sqlUpdate UTF8String], -1, &statement, NULL) != SQLITE_OK)
		NSAssert1(0, @"Error while creating update statement. '%s'", sqlite3_errmsg(database));
		
	if(SQLITE_DONE != sqlite3_step(statement))
		NSAssert1(0, @"Error while updating. '%s'", sqlite3_errmsg(database));
	
				  
	sqlite3_finalize(statement);
}

- (NSMutableArray *) getRegistrations: (NSInteger)userId
{
	NSMutableArray *registrationArray = [[NSMutableArray alloc] init];
	
	NSString *sql = [[NSString alloc] initWithFormat: @"SELECT Courses.CourseId,Courses.Date,Courses.Time,Courses.Location,Courses.Description, Courses.CourseHeader \
					 FROM Courses WHERE Courses.Registered=%d AND Courses.UserId=%d", 1, userId];
	
	sqlite3_stmt *statement;
	int sqlResult = sqlite3_prepare_v2(database, [sql UTF8String], -1, &statement, NULL);
	
	if(sqlResult == SQLITE_OK)
	{
		while(sqlite3_step(statement) == SQLITE_ROW)
		{
			Course *course = [[Course alloc] init];
			
			char *date = (char *)sqlite3_column_text(statement, 1);
			char *time = (char *)sqlite3_column_text(statement, 2);
			char *location = (char *)sqlite3_column_text(statement, 3);
			char *description = (char *)sqlite3_column_text(statement, 4);
			char *header = (char *)sqlite3_column_text(statement, 5);
			
			
			
			course.courseId = sqlite3_column_int(statement, 0);
			course.date = (date) ? [NSString stringWithUTF8String:date] : @"";
			course.time = (time) ? [NSString stringWithUTF8String:time] : @"";
			course.location = (location) ? [NSString stringWithUTF8String:location] : @"";
			course.description = (description) ? [NSString stringWithUTF8String:description] : @"";
			course.courseHeader = (header) ? [NSString stringWithUTF8String:header] : @"";
			
			[registrationArray addObject:course];
			[course release];
		}
		sqlite3_finalize(statement);
	}
	else 
	{
		NSLog(@"Problem with the database");
		NSLog(@"%d", sqlResult);
	}	
	return registrationArray;
}

@end
